package net.scribblemedia.sqldsk.service;

import static net.scribblemedia.sqldsk.domain.ReportRow.findReportRowsByReportQueryAndRowHash;
import static net.scribblemedia.sqldsk.domain.ReportRowState.OUTSTANDING;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import net.scribblemedia.sqldsk.domain.DatabaseConfig;
import net.scribblemedia.sqldsk.domain.ReportQuery;
import net.scribblemedia.sqldsk.domain.ReportRow;
import net.scribblemedia.sqldsk.domain.ReportRowColumn;

import org.apache.commons.codec.digest.DigestUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class SqlReportRunnerService {
	
	@Autowired
	private DatabaseConnectionService databaseConnectionService;

	@Transactional
	public void clearReports(DatabaseConfig databaseConfig) {
		List<ReportQuery> reportQueries = databaseConfig.getReportQueries();
		for (ReportQuery reportQuery : reportQueries) {
			reportQuery.removeAllReportRows();
		}
	}
	
	public void runReportQuery(DatabaseConfig databaseConfig) {
		if (!databaseConfig.isEnabled()) {
			return;
		}
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate(databaseConnectionService.buildDataSource(databaseConfig));
		
		for (ReportQuery reportQuery : databaseConfig.getReportQueries()) {
			runReportQuery(reportQuery, jdbcTemplate);
		}
	}
	
	private void runReportQuery(ReportQuery reportQuery, JdbcTemplate jdbcTemplate) {
		Collection<Map<String, String>> rows = executeQuery(reportQuery, jdbcTemplate);
		
		for (Map<String, String> row : rows) {
			createAndPersistRow(reportQuery, row);
		}
	}
	
	@Transactional
	private void createAndPersistRow(ReportQuery reportQuery, Map<String, String> row) {
		StringBuffer buf = new StringBuffer();
		for (String column : row.values()) {
			buf.append(column);
		}
		
		String md5Hash = DigestUtils.md5Hex(buf.toString());
		List<ReportRow> existingReportRow = findReportRowsByReportQueryAndRowHash(reportQuery, md5Hash).getResultList();
		
		if (existingReportRow == null || existingReportRow.isEmpty()) {
			createNewReportRow(reportQuery, row, md5Hash);
		}
	}

	@Transactional
	private void createNewReportRow(ReportQuery reportQuery, Map<String, String> row, String md5Hash) {
		ReportRow newReportRow = new ReportRow();
		newReportRow.setReportQuery(reportQuery);
		newReportRow.setReportRowState(OUTSTANDING);
		newReportRow.setRowHash(md5Hash);
		
		newReportRow.persist();
		
		reportQuery.addReportRow(newReportRow);
		
		for (String columnName : row.keySet()) {
			String columnValue = row.get(columnName);
			ReportRowColumn reportRowColumn = new ReportRowColumn();
			reportRowColumn.setValue(columnValue);
			reportRowColumn.setColumnName(columnName);
			reportRowColumn.persist();
			newReportRow.addReportRowColumn(reportRowColumn);
		}
	}

	private Collection<Map<String, String>> executeQuery(ReportQuery reportQuery, JdbcTemplate jdbcTemplate) {
		Collection<Map<String, String>> rows = jdbcTemplate.query(reportQuery.getReportSql(), new RowMapper<Map<String, String>>() {
			@Override
			public Map<String, String> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, String> row = new LinkedHashMap<String, String>();
				int columnCount = rs.getMetaData().getColumnCount();
				for (int i = 1; i <= columnCount; i++) {
					row.put(rs.getMetaData().getColumnName(i), rs.getString(i));
				}
				return row;
			}
		});
		return rows;
	}
}
